# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://doc.scrapy.org/en/latest/topics/item-pipeline.html
import pymysql


class ZhihuPipeline(object):

    def open_spider(self, spider):
        # 注意 scrapy_default 这个字段 和 3306 是数字而非字符串
        db = spider.settings.get('MYSQL_DB_NAME', 'scrapy_default')
        host = spider.settings.get('MYSQL_HOST', 'localhost')
        port = spider.settings.get('MYSQL_PORT', 3306)
        user = spider.settings.get('MYSQL_USER', 'root')
        passwd = spider.settings.get('MYSQL_PASSWORD', 'root')

        # 注意这里的 utf8 不是 utf-8,是和你建表时的要一样，否则会报错，说没有 encode 方法
        self.db_conn = pymysql.connect(host=host, port=port, db=db, user=user, password=passwd, charset="utf8")
        self.db_cur = self.db_conn.cursor()

    def process_item(self, item, spider):
        try:
            if spider.name == "zh":
                self.insert_db_zh(item)
            elif spider.name == "paper":
                self.insert_db_paper(item)
        except Exception as err:
            print("插入数据库出错："+str(err))
        return item

    def close_spider(self, spider):
        self.db_conn.commit()
        self.db_cur.close()

    def insert_db_zh(self, item):
        values = (
            item["id"],
            item["name"],
            item["url"],
        )
        sql = 'INSERT INTO topic2nd VALUES(%s, %s, %s)'
        self.db_cur.execute(sql, values)

    def insert_db_paper(self, item):
        values = (
            item["title"],
            item["url"],
            item["p_type"],
        )
        sql = 'INSERT INTO paper VALUES(%s, %s, %s)'
        self.db_cur.execute(sql, values)

